﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace CardReader.Controls
{
  class ZHCX
  {
    public ZHCX()
    {
      //
      //TODO: 在此处添加构造函数逻辑
      //
    }

    // 生成综合查询表
    public static void CreateZHCX(string sKHID, string sRQ)
    {
      string sSql = "select cx_khbm from zhcx where cx_khbm='" + sKHID + "' and convert(varchar(6),rq,112)='"
        + sRQ.Substring(0, 4) + sRQ.Substring(5, 2) + "'";
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
      if (dr.Read())
      {
      }
      else
      {
        sSql = @"insert into zhcx(cx_khbm,cx_khmc,cx_gldw,cx_khjb,cx_ic,rq,cx_bs,cx_id)
select a.khid,a.khmc,b.dwmc,c.khjb,a.ic,@1,a.kh,@2 from jcxx_khxx a,jcxx_dw b,jcxx_khjb c
where a.dwid=b.dwid and a.khjbid=c.khjbid and a.khid=@0";
        SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) 
                                 , new SqlParameter("@2", SqlDbType.VarChar, 20) };
        cmdParams[0].Value = sKHID;
        cmdParams[1].Value = sRQ;
        cmdParams[2].Value = sjlr.GetID("zhcx", "cx_id");
        int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);
      }
      dr.Close();
    }

    //生成综合查询表与抄表数据有关部分
    public static void UpdateCB(string sKHID, string sRQ, string sID)
    {
      double[] dtYS = sjlr.GetYS(sKHID, sRQ);
      //double[] dtCY = CBSJ.GetCY(sKHID, sRQ);
      double ljcy = Convert.ToDouble(sjlr.GetLJCY(sKHID, sRQ));
      ljcy = ljcy > 0 ? 0 : ljcy;
      string sSQJY = GetSQJY(sKHID, sRQ);
      string sBQSJHJ = sjlr.GetBQSJHJ(sKHID, sRQ);
      //string sBQJY = GetBQJY(sKHID, sRQ);
      string sBYBS = "0", sCBSJ = "";
      SqlDataReader dr = sjlr.GetCBSJ(sID);
      if (dr.Read())
      {
        sBYBS = dr["bybs"].ToString();
        sCBSJ = dr["cbsj"].ToString();
        //sYSQL = dr["ysql"].ToString();
      }
      dr.Close();
      string sSql = @"update zhcx set cx_ysql=@2,cx_sr=@3,cx_jgjj=@4,CX_bc=@5,
CX_bybs=@6,CX_cbsj=@7,CX_sjsyql=@8,CX_sybs=@9,CX_SLLJCY=@10,CX_JELJCY=@11,cx_cbid=@12,CX_SQJY=@13,CX_BQJY=@14
where cx_khbm=@0 and convert(varchar(6),rq,112)=@1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20),
                               new SqlParameter("@4", SqlDbType.VarChar, 20), new SqlParameter("@5", SqlDbType.VarChar, 20),
                               new SqlParameter("@6", SqlDbType.VarChar, 20), new SqlParameter("@7", SqlDbType.VarChar, 20),
                               new SqlParameter("@8", SqlDbType.VarChar, 20), new SqlParameter("@9", SqlDbType.VarChar, 20),
                               new SqlParameter("@10", SqlDbType.VarChar, 20), new SqlParameter("@11", SqlDbType.VarChar, 20),
                               new SqlParameter("@12", SqlDbType.VarChar, 20),new SqlParameter("@13", SqlDbType.VarChar, 20),
                               new SqlParameter("@14", SqlDbType.VarChar, 20)};
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      cmdParams[2].Value = dtYS[0].ToString();
      cmdParams[3].Value = dtYS[1].ToString();
      cmdParams[4].Value = dtYS[2].ToString();
      cmdParams[5].Value = dtYS[3].ToString();
      cmdParams[6].Value = sBYBS;
      cmdParams[7].Value = sCBSJ;
      cmdParams[8].Value = dtYS[0].ToString();
      cmdParams[9].Value = ZHCX.GetSYBS(sKHID, sRQ);
      cmdParams[10].Value = 0;
      cmdParams[11].Value = ljcy;
      cmdParams[12].Value = sID;
      cmdParams[13].Value = sSQJY;
      cmdParams[14].Value = (Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)) < 0 ? 0 :
        (Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ));
      int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

    }

    //上月表数
    protected static string GetSYBS(string sKHID, string sRQ)
    {
      string s = "0";
      string sSql = @"select a.* from zhcx a
where convert(varchar(6),dateadd(month,1,a.rq),112)=@1 and a.cx_khbm=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = string.IsNullOrEmpty(dr["cx_bybs"].ToString()) ? "0" : dr["cx_bybs"].ToString();
      }
      dr.Close();
      return s;
    }

    //上期结余
    protected static string GetSQJY(string sKHID, string sRQ)
    {
      string s = "0";
      string sSql = @"select a.* from zhcx a
where  a.cx_khbm=@0 and a.RQ=(select MAX(rq) from ZHCX where CX_KHBM=@0)";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      //cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = string.IsNullOrEmpty(dr["cx_bqjy"].ToString()) ? "0" : dr["cx_bqjy"].ToString();
      }
      dr.Close();
      return s;
    }

    //本期结余
    protected static string GetBQJY(string sKHID, string sRQ)
    {
      string s = "0";
      string sSql = @"select a.* from zhcx a
where convert(varchar(6),a.rq,112)=@1 and a.cx_khbm=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = string.IsNullOrEmpty(dr["cx_bqjy"].ToString()) ? "0" : dr["cx_bqjy"].ToString();
      }
      dr.Close();
      return s;
    }

    //生成综合查询表与缴费数据有关部分
    public static void UpdateJF(string sKHID, string sRQ)
    {
      double[] dtYS = sjlr.GetYS(sKHID, sRQ);
      //double[] dtSS = JFSJ.GetSS(sKHID, sRQ);
      //double[] dtCY = CBSJ.GetCY(sKHID, sRQ);
      double ljcy = Convert.ToDouble(sjlr.GetLJCY(sKHID, sRQ));
      ljcy = ljcy > 0 ? 0 : ljcy;
      string sBQSJHJ = sjlr.GetBQSJHJ(sKHID, sRQ);
      string sSQJY = GetSQJY(sKHID, sRQ);
      string sBCJY = sjlr.GetYE(sKHID);
      string sSql = @"update zhcx set cx_ssql=@2,cx_sjsr=@3,cx_sjjgjj=cast(@5 as float)-cast(@3 as float),CX_XJ=@5,
CX_BQSJ=@6,CX_SQJY=@7,CX_BQJY=@8,
CX_SLLJCY=@9,CX_JELJCY=@10
where cx_khbm=@0 and convert(varchar(6),rq,112)=@1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20),
                               new SqlParameter("@4", SqlDbType.VarChar, 20), new SqlParameter("@5", SqlDbType.VarChar, 20),
                               new SqlParameter("@6", SqlDbType.VarChar, 20), new SqlParameter("@7", SqlDbType.VarChar, 20),
                               new SqlParameter("@8", SqlDbType.VarChar, 20), new SqlParameter("@9", SqlDbType.VarChar, 20),
                               new SqlParameter("@10", SqlDbType.VarChar, 20)};
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      cmdParams[2].Value = "0";
      cmdParams[3].Value = Convert.ToDouble(sBQSJHJ) >= dtYS[1] ? dtYS[1].ToString() : sBQSJHJ;
      cmdParams[4].Value = "0";
      cmdParams[5].Value = Convert.ToDouble(sBQSJHJ) >= dtYS[3] ? dtYS[3].ToString() : sBQSJHJ;
      cmdParams[6].Value = sBQSJHJ;
      cmdParams[7].Value = sSQJY;
      cmdParams[8].Value = Convert.ToDouble(sBCJY) < 0 ? 0 : Convert.ToDouble(sBCJY);
      cmdParams[9].Value = 0;
      cmdParams[10].Value = ljcy;
      int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

    }

    //获取数据
    public static DataSet GetDS(string date1, string date2)
    {
      StringBuilder sbSql = new StringBuilder();
      sbSql.Append("select ROW_NUMBER() OVER (ORDER BY cx_id desc) AS xh,a.*,a.CX_JELJCY*-1 JELJCY from zhcx a where a.rq>=@0 and a.rq<=cast(@1 as datetime) ");
      //sbSql.Append("order by cx_id desc");
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
      cmdParams[0].Value = date1;
      cmdParams[1].Value = date2;
      DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
      return ds;
    }

    //获取抄表与收费合并
    public static DataSet GetCBSF(string khid, string nf)
    {
      string sql = @"
select cbsj,sum(bqql) ysql,sum(ysfy) ysfy,null ssfy,null bcjyk from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and khid=@0 and bqql_sfsb=2 and convert(varchar(4),a.cbsj,112)=@1 group by cbsj
union all
select jfsj,null,null,bqsj,bcjyk from sjlr_jfsj where khid=@0 and convert(varchar(4),jfsj,112)=@1
order by cbsj";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = khid;
      cmdParams[1].Value = nf;
      DataSet ds = DbHelperSQL.Query(sql, cmdParams);
      double snjz = Convert.ToDouble(sjlr.GetSNJZ(khid, nf));
      double bcjy = 0, ssje = 0, ysje = 0;
      for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
      {
        if (i == 0)
        {
          ssje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ssfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ssfy"].ToString());
          ysje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ysfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ysfy"].ToString());
          bcjy = Math.Round((ssje - ysje), 2);
          ds.Tables[0].Rows[i]["bcjyk"] = Math.Round((snjz + bcjy), 2).ToString();
        }
        else
        {
          snjz = Convert.ToDouble(ds.Tables[0].Rows[i - 1]["bcjyk"].ToString());
          ssje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ssfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ssfy"].ToString());
          ysje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ysfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ysfy"].ToString());
          bcjy = Math.Round((ssje - ysje), 2);
          ds.Tables[0].Rows[i]["bcjyk"] = Math.Round((snjz + bcjy), 2).ToString();
        }
      }
      return ds;
    }
  }
}
